package com.zr.d_c.daoimpl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.zr.d_c.dao.DishDao;
import com.zr.d_c.model.Dish;
import com.zr.d_c.util.JDBCUtil;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
 * 商家对菜品的增删改查的实现
 * @author wzp
 *
 */
public class DishDaoImpl implements DishDao {

	@Override
	public List<Dish> selsectDishBySid(int sid,int start,int pageSize) {
		// TODO Auto-generated method stub
		List<Dish> dish = new ArrayList<Dish>();
		//sql语句，查询商家下的菜品
		StringBuffer sql = new StringBuffer("select did,dname,dnum,dprice,dcreatetime,dtemp1 FROM d_cdish WHERE ");
		if(sid!=0||start!=0||pageSize!=0){
			sql.append("sid=? and disdelete='1' limit ?,?");
		}
		//打开数据库连接
		Connection con = JDBCUtil.getConnection();
		//打开流
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Dish d = new Dish();
				d.setDid(rs.getInt("did"));
				d.setDname(rs.getString("dname"));
				d.setDnum(rs.getInt("dnum"));
				d.setDprice(rs.getInt("dprice"));
				d.setDcreatetime(rs.getString("dcreatetime"));
				d.setDtemp1(rs.getString("dtemp1"));
				dish.add(d);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return dish;
	}

	@Override
	public List<Dish> selectDishBySidAndBydname(int sid, String dname,int start,int pageSize) {
		// TODO Auto-generated method stub
		List<Dish> dish = new ArrayList<Dish>();
		//sql语句，查询商家下的菜品
		StringBuffer sql = new StringBuffer("select did,dname,dnum,dprice,dcreatetime,dtemp1 FROM d_cdish WHERE ");
		if(sid!=0||start!=0||pageSize!=0||dname!=null){
			sql.append("sid=? and disdelete='1'");
			sql.append(" and dname like ?");
			sql.append(" limit ?,?");
		}
		//打开数据库连接
		Connection con = JDBCUtil.getConnection();
		//打开流
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setString(2, "%"+dname+"%");
			pst.setInt(3, start);
			pst.setInt(4, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Dish d = new Dish();
				d.setDid(rs.getInt("did"));
				d.setDname(rs.getString("dname"));
				d.setDnum(rs.getInt("dnum"));
				d.setDprice(rs.getInt("dprice"));
				d.setDcreatetime(rs.getString("dcreatetime"));
				d.setDtemp1(rs.getString("dtemp1"));
				dish.add(d);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return dish;
	}

	@Override
	public boolean insertDish(Dish dish, int sid) {
		// TODO Auto-generated method stub
		boolean flag = false;
		//sql语句，插入一行，添加菜品
		StringBuffer sql = new StringBuffer("INSERT INTO d_cdish (dname,dnum,dprice,disdelete,dcreatetime,sid,dtemp1) ");
		sql.append("values (?,?,?,?,?,?,?)");
		StringBuffer sql1 = new StringBuffer("select disdelete from d_cdish where dname=? and sid=?");
		//打开数据库连接
		Connection con = JDBCUtil.getConnection();
		//打开流
		PreparedStatement pst = null;
		PreparedStatement pst1 = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst1 = con.prepareStatement(sql1.toString());
			pst1.setString(1, dish.getDname());
			pst1.setInt(2, sid);
			ResultSet rs = pst1.executeQuery();
			if(!rs.next()){
				pst.setString(1, dish.getDname());
				pst.setInt(3, dish.getDprice());
				pst.setInt(2, dish.getDnum());
				pst.setString(4, "1");
				pst.setString(5, dish.getDcreatetime());
				pst.setInt(6, sid);
				pst.setString(7, dish.getDtemp1());
				if(pst.executeUpdate()>0){
					flag = true;
				}
			}else{
				if(rs.getString("disdelete").equals("0")){
					pst.setString(1, dish.getDname());
					pst.setInt(3, dish.getDprice());
					pst.setInt(2, dish.getDnum());
					pst.setString(4, "1");
					pst.setString(5, dish.getDcreatetime());
					pst.setInt(6, sid);
					pst.setString(7, dish.getDtemp1());
					if(pst.executeUpdate()>0){
						flag = true;
					}
				}else{
					flag = false;
				}
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		if(pst1!=null){
			try {
				pst1.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}

	@Override
	public boolean updateDish(Dish dish, int sid) {
		// TODO Auto-generated method stub
		boolean flag = false;
		//sql语句,更新菜品信息
		StringBuffer sql = new StringBuffer("update d_cdish set dname=?, dnum=?, dprice=? where sid=? and did=?");
		//sql语句，判断菜品名是否相同
		StringBuffer sql1 = new StringBuffer("select disdelete from d_cdish where dname=? and sid=?");
		//打开数据库连接
		Connection con = JDBCUtil.getConnection();
		//打开流
		PreparedStatement pst = null;
		PreparedStatement pst1 = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst1 = con.prepareStatement(sql1.toString());
			pst1.setString(1, dish.getDname());
			pst1.setInt(2, sid);
			ResultSet rs = pst1.executeQuery();
			if(!rs.next()){
				pst.setString(1, dish.getDname());
				pst.setInt(2, dish.getDnum());
				pst.setInt(3, dish.getDprice());
				pst.setInt(4, sid);
				pst.setInt(5, dish.getDid());
				if(pst.executeUpdate()>0){
					flag = true;
				}
			}else{
				if(rs.getString("disdelete").equals("0")){
					pst.setString(1, dish.getDname());
					pst.setInt(2, dish.getDnum());
					pst.setInt(3, dish.getDprice());
					pst.setInt(4, sid);
					pst.setInt(5, dish.getDid());
					if(pst.executeUpdate()>0){
						flag = true;
					}
				}
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		if(pst1!=null){
			try {
				pst1.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}

	@Override
	public boolean updateDish(Dish dish) {
		// TODO Auto-generated method stub
		boolean flag = false;
		//sql语句,更新菜品信息
		StringBuffer sql = new StringBuffer("update d_cdish set dname=?, dnum=?, dprice=? where did=?");
		//sql语句，判断菜品名是否相同
		StringBuffer sql1 = new StringBuffer("select dname from d_cdish where dname=?");
		//打开数据库连接
		Connection con = JDBCUtil.getConnection();
		//打开流
		PreparedStatement pst = null;
		PreparedStatement pst1 = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst1 = con.prepareStatement(sql1.toString());
			pst1.setString(1, dish.getDname());
			ResultSet rs = pst1.executeQuery();
			if(!rs.next()){
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dish.getDname());
			pst.setInt(2, dish.getDnum());
			pst.setInt(3, dish.getDprice());
			pst.setInt(4, dish.getDid());
			if(pst.executeUpdate()>0){
				flag = true;
			}
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		if(pst1!=null){
			try {
				pst1.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}

	@Override
	public boolean deleteDish(int[] did) {
		// TODO Auto-generated method stub
		boolean flag = false;
		//sql语句,删除菜品信息
		StringBuffer sql = new StringBuffer("update d_cdish set disdelete='0' where did=?");
		//打开数据库连接
		Connection con = JDBCUtil.getConnection();
		//打开流
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			for (int i = 0; i < did.length; i++) {
				pst.setInt(1, did[i]);
				if(pst.executeUpdate()>0){
					flag = true;
				}else{
					return false;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return flag;
	}
	
	@Override
	public int getDishNumBySid(int sid) {
		// TODO Auto-generated method stub
		int count=0;
		//sql语句，计算菜品数量
		StringBuffer sql = new StringBuffer("SELECT COUNT(did) co FROM d_cdish WHERE sid=? and disdelete='1'");
		//打开数据库连接
		Connection con = JDBCUtil.getConnection();
		//打开流
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				count = rs.getInt("co");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}
	
	@Override
	public int getDishNumBySidAndDname(int sid, String dname) {
		// TODO Auto-generated method stub
		int count=0;
		//sql语句，计算菜品数量
		StringBuffer sql = new StringBuffer("SELECT COUNT(did) co FROM d_cdish WHERE sid=? and disdelete='1' and dname like ?");
		//打开数据库连接
		Connection con = JDBCUtil.getConnection();
		//打开流
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setString(2, "%"+dname+"%");
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				count = rs.getInt("co");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	@Override
	public boolean insertAllDish(String path,int sid) {
		// TODO Auto-generated method stub
		 boolean success=false;
		 Workbook rwb = null;
		 File file = new File(path);
		   InputStream is;
		try {
			is = new FileInputStream(file);
			rwb = Workbook.getWorkbook(is);
			Sheet sh = rwb.getSheet(0);
			for (int i = 0; i < sh.getRows(); i++) {
				String dname = sh.getCell(0, i).getContents();
				String dnum = sh.getCell(1, i).getContents();
				String dprice = sh.getCell(2, i).getContents();
				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
				String dcreatetime = df.format(new Date());// new Date()为获取当前系统时间
				//sql语句，添加一行数据
				StringBuffer sql = new StringBuffer("INSERT INTO d_cdish(dname,dnum,dprice,disdelete,dcreatetime,sid) VALUES(?,?,?,?,?,?)");
				//sql语句，判断菜品名是否相同
				StringBuffer sql1 = new StringBuffer("select disdelete from d_cdish where dname=? and sid=?");
				//连接数据库
				Connection con = JDBCUtil.getConnection();
				//打开流
				PreparedStatement pst = con.prepareStatement(sql.toString());
				PreparedStatement pst1 = con.prepareStatement(sql1.toString());
				pst1.setString(1, dname);
				pst1.setInt(2, sid);
				ResultSet rs = pst1.executeQuery();
				if(!rs.next()){
					pst.setString(1, dname);
					pst.setInt(2, Integer.parseInt(dnum));
					pst.setInt(3, Integer.parseInt(dprice));
					pst.setString(4, "1");
					pst.setString(5, dcreatetime);
					pst.setInt(6, sid);
					if(pst.executeUpdate()>0){
						success = true;
					}else{
						success = false;
						break;
					}
				}else{
					if(rs.getString("disdelete").equals("0")){
						pst.setString(1, dname);
						pst.setInt(2, Integer.parseInt(dnum));
						pst.setInt(3, Integer.parseInt(dprice));
						pst.setString(4, "1");
						pst.setString(5, dcreatetime);
						pst.setInt(6, sid);
						if(pst.executeUpdate()>0){
							success = true;
						}else{
							success = false;
							break;
						}
					}else{
						success = false;
						break;					
					}
				}
				rs.close();
				JDBCUtil.closeJDBC(pst, con);
			}
			rwb.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return success;
	}
	@Override
	public int insertImageDish(String path, int did) {
		// TODO Auto-generated method stub
		int flag = 0;
		//连接数据库
		Connection con = JDBCUtil.getConnection();
		//打开流
		PreparedStatement pst = null;
		PreparedStatement pst1 =null;
		//sql语句，判断图片名是否相同
		StringBuffer sql1 = new StringBuffer("select dtemp1 from d_cdish where dtemp1=? and did=?");
		//sql语句，对指定菜品添加图片
		StringBuffer sql = new StringBuffer("update d_cdish set dtemp1=? where did=?");
		try {
			pst = con.prepareStatement(sql1.toString());
			pst.setString(1, path);
			pst.setInt(2, did);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				flag = 0;
			}else{
				pst1 = con.prepareStatement(sql.toString());
				pst1.setString(1, path);
				pst1.setInt(2, did);
				if(pst1.executeUpdate()>0){
					flag = 1;
				}else{
					flag = -1;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return flag;
	}
@Override
	public Dish selectDishByDid(int did) {
		// TODO Auto-generated method stub
		Dish dish = new Dish();
		Connection con = null;
		PreparedStatement pst = null;
		
		StringBuffer sql = new StringBuffer("select * from d_cdish where did = ?");
		try {
			con = JDBCUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, did);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				dish.setSid(rs.getInt("sid"));
				dish.setDname(rs.getString("dname"));
				dish.setDprice(rs.getInt("dprice"));
				dish.setDtemp1(rs.getString("dtemp1"));
				dish.setDnum(rs.getInt("dnum"));
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closeJDBC(pst, con);
		}
		
		return dish;
	}

	@Override
	public void updateDnum(int did, int dnum) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pst = null;
		
		StringBuffer sql = new StringBuffer("update d_cdish set dnum = ? where did = ?");
		
		try {
			con = JDBCUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, dnum);
			pst.setInt(2, did);
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closeJDBC(pst, con);
		}
	}

	@Override
	public Dish selectDishBySidAndDname(int sid, String dname) {
		// TODO Auto-generated method stub
		Dish dish = new Dish();
		Connection con = null;
		PreparedStatement pst = null;
		
		StringBuffer sql = new StringBuffer("select * from d_cdish where sid = ? and dname = ?");
		try {
			con = JDBCUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setString(2, dname);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				dish.setSid(rs.getInt("sid"));
				dish.setDname(rs.getString("dname"));
				dish.setDprice(rs.getInt("dprice"));
				dish.setDtemp1(rs.getString("dtemp1"));
				dish.setDnum(rs.getInt("dnum"));
				dish.setDid(rs.getInt("did"));
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closeJDBC(pst, con);
		}
		
		return dish;
	}
}
